-- 数据源基本信息表
drop table T_DATASOURCE;
create table T_DATASOURCE (
    ID BIGINT NOT NULL  IDENTITY CONSTRAINT T_DATASOURCE_PK  PRIMARY KEY NONCLUSTERED,
    DB_TYPE NVARCHAR(32) NOT NULL,
    DB_NAME NVARCHAR(256) NOT NULL,
    JDBC_URL NVARCHAR(2000) NOT NULL,
    JDBC_DRIVER NVARCHAR(256) NOT NULL,
    USER_NAME NVARCHAR(256) NOT NULL,
    PASSWORD NVARCHAR(256) NOT NULL,
    REMARKS NVARCHAR(256) NOT NULL,

    STATUS NVARCHAR(2) NOT NULL,
    DELETE_FLAG NVARCHAR(2) NOT NULL,
    CREATE_TIME DATETIME NOT NULL,
    UPDATE_TIME DATETIME NOT NULL,
)
go

exec sp_addextendedproperty 'MS_Description', '用户基本信息表', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE'
exec sp_addextendedproperty 'MS_Description', '主键ID', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'ID'

exec sp_addextendedproperty 'MS_Description', '数据库类型(定义枚举类或配置属性)', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'DB_TYPE'
exec sp_addextendedproperty 'MS_Description', '数据库名称(英文名称)', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'DB_NAME'
exec sp_addextendedproperty 'MS_Description', 'jdbc的url', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'JDBC_URL'
exec sp_addextendedproperty 'MS_Description', 'jdbc的驱动类型', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'JDBC_DRIVER'
exec sp_addextendedproperty 'MS_Description', '数据库连接的账号', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'USER_NAME'
exec sp_addextendedproperty 'MS_Description', '数据库连接密码', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'PASSWORD'
exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'REMARKS'

exec sp_addextendedproperty 'MS_Description', '状态(0:无效; 1:有效)', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'STATUS'
exec sp_addextendedproperty 'MS_Description', '是否删除(0:未删除; 1:已删除)', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'DELETE_FLAG'
exec sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'CREATE_TIME'
exec sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', 'dbo', 'TABLE', 'T_DATASOURCE', 'COLUMN', 'UPDATE_TIME'
go

-- 用户基本信息表
drop table T_USER;
create table T_USER
(
    ID BIGINT NOT NULL  IDENTITY CONSTRAINT T_USER_PK  PRIMARY KEY NONCLUSTERED,
    USER_TYPE NVARCHAR(2) NOT NULL,
    USER_CODE NVARCHAR(32),
    USER_NAME NVARCHAR(32),
    USER_PHONE NVARCHAR(19) NOT NULL,
    USER_PASS NVARCHAR(128) NOT NULL,
    COMPANY_NO NVARCHAR(32),
    COMPANY_NAME NVARCHAR(64),
    OPEN_ID NVARCHAR(64),

    STATUS NVARCHAR(2) NOT NULL,
    DELETE_FLAG NVARCHAR(2) NOT NULL,
    CREATE_TIME DATETIME NOT NULL,
    UPDATE_TIME DATETIME NOT NULL,
)
go
exec sp_addextendedproperty 'MS_Description', '用户基本信息表', 'SCHEMA', 'dbo', 'TABLE', 'T_USER'
exec sp_addextendedproperty 'MS_Description', '主键ID', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'ID'
exec sp_addextendedproperty 'MS_Description', '用户类型(0:超级管理员; 1:公司管理员; 2:普通用户)', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'USER_TYPE'
exec sp_addextendedproperty 'MS_Description', '用户编号', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'USER_CODE'
exec sp_addextendedproperty 'MS_Description', '用户名称', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'USER_NAME'
exec sp_addextendedproperty 'MS_Description', '用户手机号', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'USER_PHONE'
exec sp_addextendedproperty 'MS_Description', '用户登录密码', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'USER_PASS'
exec sp_addextendedproperty 'MS_Description', '用户所属公司编号', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'COMPANY_NO'
exec sp_addextendedproperty 'MS_Description', '用户所属公司名称', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'COMPANY_NAME'
exec sp_addextendedproperty 'MS_Description', '微信授权openid', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'OPEN_ID'
exec sp_addextendedproperty 'MS_Description', '状态(0:无效; 1:有效)', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'STATUS'
exec sp_addextendedproperty 'MS_Description', '是否删除(0:未删除; 1:已删除)', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'DELETE_FLAG'
exec sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'CREATE_TIME'
exec sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', 'dbo', 'TABLE', 'T_USER', 'COLUMN', 'UPDATE_TIME'
go

INSERT INTO [dbo].[T_DATASOURCE]([ID], [DB_TYPE], [DB_NAME], [JDBC_URL], [JDBC_DRIVER], [USER_NAME], [PASSWORD], [REMARKS], [STATUS], [DELETE_FLAG], [CREATE_TIME], [UPDATE_TIME])
VALUES (1, 'MSSQL_2005', '鞋业', 'jdbc:sqlserver://114.55.173.162:1433;DatabaseName=鞋业', 'com.microsoft.sqlserver.jdbc.SQLServerDriver', 'sa', 'Ding19861015', '', '1', '0', '2020-06-18 15:14:58.000', '2020-06-18 15:15:02.000');


-- INSERT INTO guns_advanced.database_info (db_id, db_name, jdbc_driver, user_name, password, jdbc_url, remarks, create_time) VALUES (1277579248701100035, 'shoe', 'net.sourceforge.jtds.jdbc.Driver', 'sa', 'Ding19861015', 'jdbc:jtds:sqlserver://114.55.173.162:1433;DatabaseName=shoe', 'sqlserver测试数据源', '2020-06-29 20:26:43');
-- INSERT INTO guns_advanced.database_info (db_id, db_name, jdbc_driver, user_name, password, jdbc_url, remarks, create_time) VALUES (1279259420445655042, 'master', 'com.mysql.cj.jdbc.Driver', 'root', 'root', 'jdbc:mysql://127.0.0.1:3306/guns_advanced?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT', '主数据源，项目启动数据源！', '2020-07-04 11:43:07');
